﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using DevExpress.XtraCharts;
using ERP_RORZE_SYSTEM;
using System.Data;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class rpt_F4TimeMonthly : DevExpress.XtraReports.UI.XtraReport
    {
        public string year;
        public string plan;

        private DataTable AssemblyTable;
        private DataTable AssemblyTableShow;
        private DataTable AssemblyChartTable;

        public rpt_F4TimeMonthly()
        {
            InitializeComponent();
            this.year = null;
            this.plan = null;

            this.AssemblyTable = new DataTable();
            this.AssemblyTableShow = new DataTable();
            this.AssemblyChartTable = new DataTable();
        }

        private void BindData()
        {
            /// CONTROLLER
            this.ControllerReport.DataSource = this.AssemblyTableShow;

            controllerJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            controllerFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            controllerMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            controllerAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            controllerMayLabel.Text = "May-" + this.year.Substring(2, 2);
            controllerJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            controllerJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            controllerAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            controllerSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            controllerOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            controllerNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            controllerDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            itemName.DataBindings.Add("Text", this.ControllerReport.DataSource, "Items");
            controllerJan.DataBindings.Add("Text", this.ControllerReport.DataSource, "Jan-" + this.year.Substring(2, 2));
            controllerFeb.DataBindings.Add("Text", this.ControllerReport.DataSource, "Feb-" + this.year.Substring(2, 2));
            controllerMar.DataBindings.Add("Text", this.ControllerReport.DataSource, "Mar-" + this.year.Substring(2, 2));
            controllerApr.DataBindings.Add("Text", this.ControllerReport.DataSource, "Apr-" + this.year.Substring(2, 2));
            controllerMay.DataBindings.Add("Text", this.ControllerReport.DataSource, "May-" + this.year.Substring(2, 2));
            controllerJun.DataBindings.Add("Text", this.ControllerReport.DataSource, "Jun-" + this.year.Substring(2, 2));
            controllerJul.DataBindings.Add("Text", this.ControllerReport.DataSource, "Jul-" + this.year.Substring(2, 2));
            controllerAug.DataBindings.Add("Text", this.ControllerReport.DataSource, "Aug-" + this.year.Substring(2, 2));
            controllerSep.DataBindings.Add("Text", this.ControllerReport.DataSource, "Sep-" + this.year.Substring(2, 2));
            controllerOct.DataBindings.Add("Text", this.ControllerReport.DataSource, "Oct-" + this.year.Substring(2, 2));
            controllerNov.DataBindings.Add("Text", this.ControllerReport.DataSource, "Nov-" + this.year.Substring(2, 2));
            controllerDec.DataBindings.Add("Text", this.ControllerReport.DataSource, "Dec-" + this.year.Substring(2, 2));
            controllerTotal.DataBindings.Add("Text", this.ControllerReport.DataSource, this.year);

            this.controllerChart.Titles[0].Text = "Time chart " + this.year + " ASSEMBLY";

            this.controllerChart.Series["Plan"].DataSource = this.AssemblyChartTable;
            this.controllerChart.Series["Plan"].ArgumentScaleType = ScaleType.Auto;
            this.controllerChart.Series["Plan"].ArgumentDataMember = "Month";
            this.controllerChart.Series["Plan"].ValueScaleType = ScaleType.Numerical;
            this.controllerChart.Series["Plan"].ValueDataMembers.AddRange(new string[] { "Plan" });

            this.controllerChart.Series["Actual"].DataSource = this.AssemblyChartTable;
            this.controllerChart.Series["Actual"].ArgumentScaleType = ScaleType.Auto;
            this.controllerChart.Series["Actual"].ArgumentDataMember = "Month";
            this.controllerChart.Series["Actual"].ValueScaleType = ScaleType.Numerical;
            this.controllerChart.Series["Actual"].ValueDataMembers.AddRange(new string[] { "Actual" });

            this.controllerChart.Series["Standard Time (TGC)"].DataSource = this.AssemblyChartTable;
            this.controllerChart.Series["Standard Time (TGC)"].ArgumentScaleType = ScaleType.Auto;
            this.controllerChart.Series["Standard Time (TGC)"].ArgumentDataMember = "Month";
            this.controllerChart.Series["Standard Time (TGC)"].ValueScaleType = ScaleType.Numerical;
            this.controllerChart.Series["Standard Time (TGC)"].ValueDataMembers.AddRange(new string[] { "StdTime" });

            this.controllerChart.Series["Actual Time (TGTT)"].DataSource = this.AssemblyChartTable;
            this.controllerChart.Series["Actual Time (TGTT)"].ArgumentScaleType = ScaleType.Auto;
            this.controllerChart.Series["Actual Time (TGTT)"].ArgumentDataMember = "Month";
            this.controllerChart.Series["Actual Time (TGTT)"].ValueScaleType = ScaleType.Numerical;
            this.controllerChart.Series["Actual Time (TGTT)"].ValueDataMembers.AddRange(new string[] { "ActTime" });

            this.controllerChart.Series["Different Time"].DataSource = this.AssemblyChartTable;
            this.controllerChart.Series["Different Time"].ArgumentScaleType = ScaleType.Auto;
            this.controllerChart.Series["Different Time"].ArgumentDataMember = "Month";
            this.controllerChart.Series["Different Time"].ValueScaleType = ScaleType.Numerical;
            this.controllerChart.Series["Different Time"].ValueDataMembers.AddRange(new string[] { "DiffTime" });            
        }

        public void LoadData()
        {
            DataSet assemblyDataSet = new DataSet();
            
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            
            // Get data for Assembly Report
            string sql_assembly = "SELECT 'Plan' AS [Items],"
                + " " + this.plan + " AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [May-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " " + this.plan + " AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'Actual' AS [Items],"
                + " 0 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [May-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'Standard Time (TGC)' AS [Items],"
                + " 0 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [May-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [" + this.year + "]"
                + " UNION ALL"
                + " SELECT 'Actual Time (TGTT)' AS [Items],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 1 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 2 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 3 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 4 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 5 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 6 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 7 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 8 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 9 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 10 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 11 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t2.FinishedDate) = 12 THEN t1.[AssemblyTime] ELSE 0 END)/60 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(t1.[AssemblyTime])/60 AS [" + this.year + "]"
                + " FROM [231_TimePlan] AS t2 LEFT JOIN"
                + " [231_TimeInput] AS t1 ON t1.TimePlanNo = t2.TimePlanNo"
                + " WHERE (DATEPART(year, t2.FinishedDate) = " + this.year + ") AND (t1.F5 = 0)"
                + " UNION ALL"
                + " SELECT 'Different time' AS [Items],"
                + " 0 AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [May-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " 0 AS [" + this.year + "]";

            assemblyDataSet = mySQLManagement.SQL_Query(sql_assembly);
            if (assemblyDataSet != null)
            {
                AssemblyTable = assemblyDataSet.Tables[0];

                for (int i = 1; i < this.AssemblyTable.Columns.Count; i++)
                {
                    double std = Double.Parse(this.AssemblyTable.Rows[2][i].ToString());
                    double act = Double.Parse(this.AssemblyTable.Rows[3][i].ToString());

                    this.AssemblyTable.Rows[4][i] = std - act;

                    if (std != 0)
                    {
                        this.AssemblyTable.Rows[1][i] = Math.Round(((std - act) / std) * 100, 2);
                    }
                }

                this.AssemblyTableShow = this.AssemblyTable.Clone();
                for (int i = 0; i < AssemblyTableShow.Columns.Count; i++)
                {
                    AssemblyTableShow.Columns[i].DataType = typeof(string);
                }

                foreach (DataRow target_row in this.AssemblyTable.Rows)
                {
                    AssemblyTableShow.ImportRow(target_row);
                }

                for (int i = 1; i < AssemblyTableShow.Columns.Count; i++)
                {
                    AssemblyTableShow.Rows[0][i] = AssemblyTableShow.Rows[0][i].ToString() + "%";
                    AssemblyTableShow.Rows[1][i] = AssemblyTableShow.Rows[1][i].ToString() + "%";
                }

                AssemblyChartTable.Columns.Add("Month", typeof(string));
                AssemblyChartTable.Columns.Add("Plan", typeof(double));
                AssemblyChartTable.Columns.Add("Actual", typeof(double));
                AssemblyChartTable.Columns.Add("StdTime", typeof(double));
                AssemblyChartTable.Columns.Add("ActTime", typeof(double));
                AssemblyChartTable.Columns.Add("DiffTime", typeof(double));

                for (int i = 1; i < AssemblyTable.Columns.Count - 1; i++)
                {
                    DataRow assembly_chart_row = AssemblyChartTable.NewRow();
                    assembly_chart_row["Month"] = AssemblyTable.Columns[i].ColumnName;
                    assembly_chart_row["Plan"] = Double.Parse(AssemblyTable.Rows[0][i].ToString()) / 100;
                    assembly_chart_row["Actual"] = Double.Parse(AssemblyTable.Rows[1][i].ToString()) / 100;
                    assembly_chart_row["StdTime"] = Double.Parse(AssemblyTable.Rows[2][i].ToString());
                    assembly_chart_row["ActTime"] = Double.Parse(AssemblyTable.Rows[3][i].ToString());
                    assembly_chart_row["DiffTime"] = Double.Parse(AssemblyTable.Rows[4][i].ToString());
                    AssemblyChartTable.Rows.Add(assembly_chart_row);                    
                }
            }

            BindData();
        }
    }
}
